package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.repository.SlopeRepository;
import com.css.fxfzypg.modules.baseData.entity.Slope;
import com.css.fxfzypg.modules.baseData.queryParams.SlopeQueryParams;
import com.css.fxfzypg.modules.baseData.vo.SlopeVO;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class SlopeRepositoryImpl implements SlopeRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public SlopeVO updateSlope(SlopeVO slopeVO) {
        String sql = "update sys_slope set slope_number = :slopeNumber, slope_band = :slopeBand, slope_nodatavalue = :slopeNodatavalue, slope_min = :slopeMin, slope_max = :slopeMax  where slope_id = '" + slopeVO.getSlopeId() + "'";

        SqlParameterSource source = new BeanPropertySqlParameterSource(slopeVO);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return slopeVO;
    }

    @Override
    public Slope saveSlope(Slope slope) {
        String sql = "insert into sys_slope values(:slopeId,:slopeNumber,:slopeBand,:slopeNodatavalue,:slopeMin,:slopeMax,:slopeStatus)";

        SqlParameterSource source = new BeanPropertySqlParameterSource(slope);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return slope;
    }

    @Override
    public Page<Slope> getAllSlopeParams(SlopeQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  s.* from sys_slope s ");

        StringBuilder orderSql = new StringBuilder(" order by s.slope_number");

        StringBuilder whereSql = new StringBuilder(" where s.slope_status = '" + YNEnum.Y.toString() + "'");

        if (!PlatformObjectUtils.isEmpty(queryParams.getSlopeBand())) {
            whereSql.append(" and s.slope_band like '%" + queryParams.getSlopeBand() + "%'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getSlopeNodatavalue())) {
            whereSql.append(" and s.slope_nodatavalue like '%" + queryParams.getSlopeNodatavalue() + "%'");
        }

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<Slope> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(Slope.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());

        Page<Slope> slopes = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());

        return slopes;
    }

    @Override
    public Slope getSlopeById(String id) {
        String sql = "select * from sys_slope where slope_id = '" + id + "'";
        Slope slope = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Slope.class));
        return slope;
    }

    @Override
    public void deleteSlopesById(List<String> ids, Timestamp timestamp) {
        String sql = "update sys_slope set slope_status = '" + YNEnum.N + "',slope_updatetime = '" + timestamp + "' " + " where slope_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<Slope> getAll() {
        return jdbcTemplate.query("select * from sys_slope", new BeanPropertyRowMapper<>(Slope.class));
    }

    @Override
    public void importExcel(List<Slope> slopes) {
        jdbcTemplate.batchUpdate("insert into sys_slope (slope_id,slope_number,slope_band,slope_nodatavalue,slope_min,slope_max,slope_status)values(?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return slopes.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                //ps.setString(1, slopes.get(i).getSlopeId());
                ps.setInt(2, slopes.get(i).getSlopeNumber());
                /*ps.setString(3,slopes.get(i).getSlopeBand());
                ps.setString(4,slopes.get(i).getSlopeNodatavalue());
                ps.setInt(5,slopes.get(i).getSlopeMin());
                ps.setInt(6,slopes.get(i).getSlopeMax());
                ps.setString(7,slopes.get(i).getSlopeStatus());*/
            }
        });
    }

    @Override
    public List<Slope> getByTaskId(String taskId) {
        String sql = "SELECT *,ST_AsText(st_intersection((select st_geometryfromtext(task_region,4490) " +
                "FROM ypg_preassess_task WHERE id = '"+taskId+"' and del_flag='0'),s.geom)) geomText " +
                "FROM (SELECT * FROM sys_slope_union t WHERE ST_Intersects(t.geom,(select a.geom FROM " +
                "ypg_preassess_task a WHERE a.id = '"+taskId+"' and a.del_flag='0'))) s";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Slope.class));
    }

}
